import { ALL, Body, Inject, Post, Provide } from '@midwayjs/decorator';
import { CoolController, BaseController, BaseService } from 'midwayjs-cool-core';
import { Context } from 'vm';
import { classgroupStudentsEntity } from '../../../classgroupStudents/entity/classgroupStudents';
import { ClassroomEntity } from '../../../classroom/entity/classroom';
import { CourseTypeEntity } from '../../../courseType/entity/courseType';
import { classgroupEntity } from '../../entity/classgroup';
import { ClassGroupService } from '../../service/classgroup';

/**
 * 描述
 */
@Provide()
@CoolController({
  api: ['add', 'delete', 'update', 'info', 'list', 'page'],
  entity: classgroupEntity,
  service: ClassGroupService,
  before: ctx => {
    // 将前端的数据转JSON格式存数据库
    // const { data } = ctx.request.body;
    let data = ctx.request.body;

    ctx.request.body.data = JSON.stringify(data);
  },
  pageQueryOp: {
    // 让title字段支持模糊查询
    keyWordLikeFields: ['a.class_name'],
    // 让type字段支持筛选
    fieldEq: ['class_number'],
    // 指定返回字段
    select: ['a.*', 'b.*'],
    // // 关联表用户表
    leftJoin: [{
      entity: classgroupStudentsEntity,
      alias: 'b',
      condition: 'a.id = b.classgroup_id'
    }],
    where: async (ctx: Context) => {
      return [
        ['a.isDelete = :isDelete', { isDelete: false }]
      ]
    }
  },
  listQueryOp: {
    // 让type字段支持筛选
    fieldEq: ['b.student_id', 'a.class_date', 'a.class_course_type'],
    // 指定返回字段
    select: ['a.*', 'b.student_id', 'c.name AS classroomName', 'd.course_id'],
    // // 关联表用户表
    leftJoin: [{
      entity: classgroupStudentsEntity,
      alias: 'b',
      condition: 'a.id = b.classgroup_id'
    }, {
      entity: ClassroomEntity,
      alias: 'c',
      condition: 'a.class_class_classroom = c.id'
    }, {
      entity: CourseTypeEntity,
      alias: 'd',
      condition: 'a.class_course_type = d.id'
    }],
    where: async (ctx: Context) => {
      const body_data: any = JSON.parse(ctx.request.body.data)
      console.log(body_data, '111111111');
      let ret: any = [
        ['a.isDelete = :isDelete', { isDelete: false }]
      ]
      if (body_data.student_id) {
        ret.push(['b.student_id = :studentId', { studentId: body_data.student_id }])
      }
      if (body_data.class_course_type) {
        ret.push(['a.class_course_type = :class_course_type', { class_course_type: body_data.class_course_type }])
      }
      if (body_data.class_date) {
        ret.push(['a.class_date = :class_date', { class_date: body_data.class_date }])
      }
      return ret
    }
  }
})
export class classgroupController extends BaseController {
  @Inject()
  service: BaseService

  @Post('/classgroupViewList')
  async classgroupViewList(@Body(ALL) queryObject: any) {
    // 请求体中获取学生id
    const { class_date } = queryObject
    console.log(class_date, 'class_date------');

    const info = await this.service.nativeQuery(
      `
      select a.id,a.class_number,a.class_level,a.class_start_time,a.class_end_time,a.class_name,class_week,a.class_date,group_concat(c.id,'&',c.name Separator '-') as classgroup_students,a.class_teacher,a.class_class_classroom from classgroup a
      left join classgroup_students b on a.id = b.classgroup_id
      left join student c on b.student_id = c.id
      where a.class_date = '${class_date}' and a.isDelete = false
      group by a.id
      order by a.class_course_type asc,a.class_number asc
      `
    )
    // const info = await this.service.nativeQuery(
    //   `
    //   select class_week,group_concat(class_start_time,class_end_time) from classgroup group by class_week
    //   `
    // )

    return { code: 1000, data: info }
  }
  // 获取教室上课班级
  @Post('/teacherClassgroup')
  async teacherClassgroup(@Body(ALL) queryObject: any) {
    // 请求体中获取学生id
    const { class_date } = queryObject
    console.log(class_date, 'class_date------');

    const info = await this.service.nativeQuery(
      `
      select class_teacher,sum(id) from classgroup 
      where isDelete = false and class_date = '${class_date}'
      group by class_teacher
      `
    )
    // const info = await this.service.nativeQuery(
    //   `
    //   select class_week,group_concat(class_start_time,class_end_time) from classgroup group by class_week
    //   `
    // )

    return { code: 1000, data: info }
  }

  // 获取教室上课班级
  @Post('/classDeduction')
  async classDeduction(@Body(ALL) queryObject: any) {
    // 请求体中获取学生id
    const { class_date, time,class_type } = queryObject
    console.log(class_date, 'class_date------');
    console.log(time, 'time------');
    let sql = `select count(a.id) as num,b.name,c.classenNum,d.class_students,e.classed_students,f.leaveStudents,g.placementStudents from classgroup a
    left join teacher b on a.class_teacher = b.id
    left join (
      select a.teacher,count(a.id) as classenNum from class_teaching a
      where a.class_date >= '${time[0]}' and a.class_date <= '${time[1]}' and a.isDelete = false
      group by a.teacher
    ) c on a.class_teacher = c.teacher
    left join (
      select a.teacher,sum(b.class_students) as class_students from class_teaching a
      left join (
        select class_teaching,count(student) as class_students from student_class_detail
        where isDelete = false and isPlacement = false
        group by class_teaching
      ) b on a.id = b.class_teaching`
      if (class_date !== ""){
        sql = sql + `
          where a.isDelete = false and a.class_date >= '${time[0]}' and a.class_date <= '${time[1]}'
        `
      }else if (class_type === ""){
        sql = sql + `
          where a.isDelete = false and a.class_date >= '${time[0]}' and a.class_date <= '${time[1]}'
        `
      }else if (class_date === ""){
        sql = sql + `
          where a.isDelete = false
        `
      }else{
        sql = sql + `
          where a.isDelete = false
        `
      }
      
      
      sql = sql + `group by a.teacher
    ) d on a.class_teacher = d.teacher
    left join (
      select a.teacher,sum(b.classed_students) as classed_students from class_teaching a 
      left join (select class_teaching,count(student) as classed_students from student_class_detail
      where isDelete = false and isLeave = false
      group by class_teaching) b on a.id = b.class_teaching
      where a.isDelete = false and a.class_date >= '${time[0]}' and a.class_date <= '${time[1]}'
      group by a.teacher
    ) e on a.class_teacher = e.teacher`
    sql = sql + `
    left join (
      select a.teacher,sum(b.leaveStudents) as leaveStudents from class_teaching a
      left join (
        select class_teaching,count(id) as leaveStudents from student_class_detail
        where isDelete = false and isLeave = true
        group by class_teaching
      ) b on a.id = b.class_teaching
      where a.isDelete = false and a.class_date >= '${time[0]}' and a.class_date <= '${time[1]}'
      group by a.teacher
    ) f on a.class_teacher = f.teacher
    `
    sql = sql + `
    left join (
      select a.teacher,sum(b.placementStudents) as placementStudents from class_teaching a
      left join (
        select class_teaching,count(id) as placementStudents from student_class_detail
        where isDelete = false and isPlacement = true 
        group by class_teaching
      ) b on a.id = b.class_teaching
      where a.isDelete = false and a.class_date >= '${time[0]}' and a.class_date <= '${time[1]}'
      group by a.teacher
    ) g on a.class_teacher = g.teacher
    `
    if (class_date !== "" && class_type !== "") {
      sql = sql +
        `
        where a.class_date = '${class_date}' and a.isDelete = false and a.class_type = '${class_type}'
        group by a.class_teacher
        `
    }else  if(class_type === "") {
      sql = sql +
        `
        where a.class_date = '${class_date}' and a.isDelete = false 
        group by a.class_teacher
        `
    }else  if(class_date === "") {
      sql = sql +
      `
      where a.isDelete = false and a.class_type = '${class_type}'
      group by a.class_teacher
      `
    } else {
      sql = sql +
        `
        where a.isDelete = false
        group by a.class_teacher
        `
    }

    let info = await this.service.nativeQuery(sql)



    return { code: 1000, data: info }
  }
}
